
package com.sms.server;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import jxl.write.WriteException;

import org.apache.log4j.Logger;

/**
 * 
 * @author home
 *
 */
public class ExcelReportGenerator extends JExcelExportDAO {

	private final static Logger LOG = Logger.getLogger(ExcelReportGenerator.class);
	protected final static int EXCEL_ROW_LIMIT = 63000;
	protected final DateFormat sdf = new SimpleDateFormat("MMddyyyy_HHmmss");

	
	/**
	 *
	 * @param folderpath - Temp path to store the excel file 
	 * @param reportCode - Excel report code to get the SQL Query
	 * @param paramValues - List of parameters for the sql query
	 * @return
	 * @throws IOException
	 * @throws SQLException
	 * @throws WriteException
	 */
	public void createWorkBook(List headerNames, List<List> rowDatas,ByteArrayOutputStream baos)
			throws IOException,WriteException {
		try{ 
//			File inputWorkbook = new File("C:/Users/home/Desktop/lars.xls");
			int rowIndex = 0;
			this.createWorkbook(baos);
			
//			this.createSheet( "sheet1");
			this.setHeader(headerNames, rowIndex);
			rowIndex++;
			if(rowDatas.size() > 0)
				setRowValues(rowDatas, rowIndex);
		}
		catch(Exception e) {
			e.printStackTrace();
			LOG.error("Error occcurred while while create workbook : " + e);
			
		} finally {
			this.closeWorkbook();
		}
		//return fileName;
	}

	/**
	 *
	 * @param rSet - result set to get the values and set it on the excel cells
	 * @param metaData - it is used to get the details of the resultset
	 * @param rowIndex - Excel row index
	 * @throws SQLException
	 * @throws WriteException
	 */	
	protected void setRowValues(List<List> rowData,
			int rowIndex) throws SQLException, WriteException {
		for(List<Object> rowValues : rowData){
			
			int columnIndex = 0;
			for(Object object : rowValues){
				if(object != null)
					this.setCellValue(columnIndex, rowIndex, object);
				else
					this.setCellValue(columnIndex, rowIndex, "");
				columnIndex++;
			}
			rowIndex++;
		}
	}

	/**
	 *
	 * @param pStmt - Prepare statement to set the parameters
	 * @param paramValues - parameter values
	 * @return resultset 
	 * @throws org.hibernate.HibernateException
	 * @throws SQLException
	 */
	private ResultSet setParameters(PreparedStatement pStmt,
			List<Object> paramValues)
			throws org.hibernate.HibernateException, SQLException {
			
			int indx = 1;
			for (Object obj : paramValues) {
				if (obj instanceof Integer)
					pStmt.setInt(indx, (Integer) obj);
				else if (obj instanceof Long)
					pStmt.setLong(indx, (Long) obj);
				else if (obj instanceof Float)
					pStmt.setFloat(indx, (Float) obj);
				else if (obj instanceof Double)
					pStmt.setDouble(indx, (Double) obj);
				else if (obj instanceof Boolean)
					pStmt.setBoolean(indx, (Boolean) obj);
				else if (obj instanceof Date)
					pStmt.setTimestamp(indx, new java.sql.Timestamp(((Date) obj).getTime()));
				else if (obj instanceof java.sql.Date)
					pStmt.setDate(indx, (java.sql.Date) obj);
				else if(obj instanceof String)
					pStmt.setString(indx, (String) obj);
				indx++;
			}
			return pStmt.executeQuery();
		
	}

	/**
	 * @param metaData - To set the column as header name  
	 * @param rowNum - Row number to load the header
	 * @throws SQLException
	 * @throws WriteException
	 */
	protected void setHeader(ResultSetMetaData metaData, int rowNum) throws SQLException, WriteException {
		for (int i = 1; i <= metaData.getColumnCount(); i++) {
			this.setColumnHeader(i-1, rowNum, metaData.getColumnName(i));
		}
	}
	
	protected void setHeader(List<Object> headerNames, int rowNum) throws SQLException, WriteException {
		for (int i = 0; i < headerNames.size(); i++) {
			this.setColumnHeader(i, rowNum, headerNames.get(i).toString());
		}
	}
}


